package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import vcp.entity.Complaint;
import vcp.entity.Customer;
import vcp.entity.Subscription;
import vcp.entity.User;
import vcp.enums.UserType;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.CustomerTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.Constants.UserTable;
import vcp.server.utility.SQLQueries;

/**
 * <p>
 * customer DAO class implements all relevant sql queries
 * </p>
 * 
 * @author VCP team
 * 
 */
public class CustomerDAO implements IGenericDAO <Customer, User>
{
	/**
	 * update customer's status to be business class
	 * 
	 * @param ID
	 * @param bussinessClass
	 * @return true if success
	 */
	public boolean updateBussinessClass ( int ID, int bussinessClass )
	{
		boolean success = false;

		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.CUSTOMER
					, CustomerTable.BUSSINESS_CLASS + "=" + bussinessClass
					, CustomerTable.ID + "=" + String.valueOf(ID));
		success = this.refresh(sqlQuery);

		return success;
	}

	/**
	 * updates customer account balance
	 * 
	 * @param ID
	 * @param newBalance
	 * @return true if success
	 */
	public boolean updateCustomerBalance ( int ID, float newBalance )
	{
		boolean success = false;

		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.CUSTOMER
					, CustomerTable.BALANCE + "=" + newBalance
					, CustomerTable.ID + "=" + String.valueOf(ID));
		success = this.refresh(sqlQuery);

		return success;
	}

	/****************************************************************************************************************************
	 *** IGenericDAO methods ***
	 ****************************************************************************************************************************/

	/**
	 * Not implemented
	 */
	@Override
	public Integer findMaxID ()
	{
		return null;
	}

	@Override
	public Customer create ()
	{
		return new Customer();
	}

	@Override
	public Customer findByEntity ( User entity )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.CUSTOMER
					, CustomerTable.ID + "=" + String.valueOf(entity.getUserID()));

		Customer customer = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				customer = this.create();

				customer.setUserID(entity.getUserID());
				customer.setPassword(entity.getPassword());
				customer.setUserFirstName(entity.getUserFirstName());
				customer.setUserLastName(entity.getUserLastName());
				customer.setUserEmail(entity.getUserEmail());
				customer.setUserType(entity.getUserType());

				customer.setCustomerID(set.getInt(CustomerTable.ID));
				customer.setPhoneNo(set.getString(CustomerTable.PHONE));
				customer.setBussinessClass(set.getInt(CustomerTable.BUSSINESS_CLASS));
				customer.setBalance(set.getFloat(CustomerTable.BALANCE));

				SubscriptionDAO subscriptionDAO = new SubscriptionDAO();
				List <Subscription> subscriptionList = subscriptionDAO.findAll(customer);
				customer.setSubscriptions(subscriptionList);

				ComplaintDAO complaintDAO = new ComplaintDAO();
				List <Complaint> complaintList = complaintDAO.findAll(customer);
				customer.setComplaints(complaintList);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return customer;
	}

	@Override
	public Customer findByID ( int ID )
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public Customer findByID ( String ID )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__TABLES__WHERE__COMPARE_
					, TableNames.USER + "," + TableNames.CUSTOMER
					, CustomerTable.ID + "=" + ID + " " + SQLQueries.AND + UserTable.ID + "=" + ID);

		Customer customer = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				customer = this.create();

				customer.setUserID(set.getInt(UserTable.ID));
				customer.setPassword(set.getString(UserTable.PASSWORD));
				customer.setUserFirstName(set.getString(UserTable.FIRST_NAME));
				customer.setUserLastName(set.getString(UserTable.LAST_NAME));
				customer.setUserEmail(set.getString(UserTable.EMAIL));
				customer.setUserType(UserType.convert(set.getString(UserTable.TYPE)));

				customer.setCustomerID(set.getInt(CustomerTable.ID));
				customer.setPhoneNo(set.getString(CustomerTable.PHONE));
				customer.setBussinessClass(set.getInt(CustomerTable.BUSSINESS_CLASS));
				customer.setBalance(set.getFloat(CustomerTable.BALANCE));

				SubscriptionDAO subscriptionDAO = new SubscriptionDAO();
				List <Subscription> customerSubscriptionList = subscriptionDAO.findAll(customer);
				customer.setSubscriptions(customerSubscriptionList);

				ComplaintDAO complaintDAO = new ComplaintDAO();
				List <Complaint> complaintList = complaintDAO.findAll(customer);
				customer.setComplaints(complaintList);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return customer;
	}

	@Override
	public List <Customer> findAll ( User info )
	{
		return null;
	}

	/**
	 * Not implemented
	 */
	@Override
	public boolean save ( Customer entity )
	{
		boolean result = false;
		String valuesSet =
				String.format(
					"%d,\"%s\",%d,%f"
					, entity.getCustomerID()
					, entity.getPhoneNo()
					, entity.getBussinessClass()
					, entity.getBalance());

		String sqlQuery =
				String.format(
					SQLQueries.INSERT_INTO__TABLENAME__VALUES__
					, TableNames.CUSTOMER
					, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh ( String sqlQuery )
	{
		boolean success = false;

		try
		{
			success = DBAdapter.getAdapter().refresh(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return success;
	}

	@Override
	public boolean deleteByID ( int ID )
	{
		return this.deleteByID(String.valueOf(ID));
	}

	@Override
	public boolean deleteByID ( String ID )
	{
		String sqlQuery =
				String.format(
					SQLQueries.DELETE_FROM__TABLENAME__WHERE__COMPARE_
					, TableNames.CUSTOMER
					, CustomerTable.ID + "=" + ID);

		boolean success = false;
		try
		{
			success = DBAdapter.getAdapter().delete(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return success;
	}
}
